﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;
using System.Data.OracleClient;
using System.IO;
using System.Data.OleDb;

namespace DslamPort.Models
{
    public class BaseClass
    {
        public static OracleConnection ConnectDataBase()
        {
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DslamPort"].ConnectionString);
            return conn;
        }
        public static OracleConnection conn_global;
        public static int doicong(int card,int loai)
        {
            if (loai==2)//mini

            {
                return (card - 1) * 32;
            }
            if (loai==1)//ip
            {
                int a = (card * 64) ;
                if (a>448)
                {
                    a -= 128;
                }
                return a;
            }
            if (loai == 3)//msan
            {
                return (card * 64) - 1;
            }
            return -1;
        }
        public static DataSet Lib_GetDataSet(string sql)
        {
            conn_global = BaseClass.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn_global);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn_global.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            return ds;
        }
        public static void Lib_ExecuteNonQuery(string sql)
        {
            conn_global = BaseClass.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn_global);
            try
            {
                conn_global.Open();
               
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
             omd.ExecuteNonQuery();
            conn_global.Close();
        }

        public static DataTable Lib_GetDataReader(string sql)
        {
            try
            {
                using (var cnn = ConnectDataBase())
                {
                    DataTable dt = new DataTable();
                    OracleCommand cmd = new OracleCommand(sql, cnn);
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    da.Fill(dt);
                    return dt;
                }

            }
            catch (Exception)
            {
                return null;
            }
        }
        public static int Lib_CheckExist(string sql)
        {
            conn_global = BaseClass.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn_global);
            try
            {
                conn_global.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            OracleDataReader odr = omd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(odr);
            conn_global.Close();
            return dt.Rows.Count;
        }
        //file vào xuất ra data
        public static DataSet Xl_File(HttpPostedFileBase file)
        {
            string ConStr = "";

            string ext = Path.GetExtension(file.FileName).ToLower();

            string path = System.Web.HttpContext.Current.Server.MapPath("~/UPLOAD/" + file.FileName);
            file.SaveAs(path);
            if (ext.Trim() == ".xls")
            {
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                return null;
            }
            string query = "SELECT * FROM [Data$]";
            OleDbConnection conn = new OleDbConnection(ConStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        //ham xu ly luu vet
        public static void LuuVet(int id_user, int luuvet_type,DateTime date, string ghichu)
        {
            string sql = "INSERT INTO luuvet";
            sql += " select nvl((select max(id)+1 from luuvet),1),'" + id_user + "','" + luuvet_type + "',TO_DATE( '" + date + "', 'mm/dd/yyyy hh24:mi:ss'),'" + ghichu + "' from dual";
            BaseClass.Lib_ExecuteNonQuery(sql);
        }
    }
}
